package com.nuoniu.sibanyun.mapper.provider;

import com.nuoniu.sibanyun.common.exception.NuoNiuException;
import com.nuoniu.sibanyun.common.lang.StringUtils;
import com.nuoniu.sibanyun.entity.dto.DynamicVoucherDto;
import com.nuoniu.sibanyun.entity.dto.ErpVoucherDesignSearchDto;
import com.nuoniu.sibanyun.entity.erp.ErpVoucherDesign;
import com.nuoniu.sibanyun.entity.erp.ErpVoucherTable;
import com.nuoniu.sibanyun.userThread.UserInfo;
import com.nuoniu.sibanyun.userThread.UserThreadLocal;
import org.apache.ibatis.jdbc.SQL;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName VoucherDesignDynamicProvider
 * @Description 单据  动态SQL
 * @Authpr baiqiang
 * @Date 2020/11/13 17:18
 **/
public class VoucherDesignDynamicProvider {


//-- 1.普通采购 2.固定资产 3.普通销售
//-- 4.领料 5.退料 6.超领 7.零星
//-- 9.生成完工
//-- 10.自制生产 11.委外加工 12.其它
//-- 13.正常加工
//-- 14.整单委外  15.工序委外
//-- 96:销售退货(冲抵) 97: 采购退货(冲抵) 98.销售退货 99.采购退货 100:期初
//-- 101 盘点入库  106：调拨入库   	(OtherReceiveVoucher)
//-- 102 盘点出库  107：调拨出库		(OtherDispatchVoucher)
    private static final  String ID_FIELD = "Id";
    private static final  String NAME_FIELD = "Name";

    /** 校验 数字 用逗号 隔开 的字符串 */
    public static final String STR_ENG_PATTERN="^\\d+(,\\d+)*$";

    //采购业务单据(包含退货业务)
    private static final  String[] PuVoucherCodesArr
            = {"PuArrival","PurchaseInvoice","PurchaseReceiveVoucher"};

    //销售业务单据(包含退货业务)
    final private String[] SaVoucherCodesArr
            = {"SaleDelivery","SaleInvoice","SaleDispatchVoucher"};

    final private String[] FVoucherCodeArr = {"ReceiveVoucher", "PaymentVoucher", "ReceivableVoucher", "PayableVoucher", "OtherReceivableVoucher", "OtherPayableVoucher","ProcedurePrice"};

    /**
     * 没有 来源单据的 动态单据设计
     */
    private final String[] noSourceVoucherArr = {"ProductionWorkReport","InventoryAllocation","ProcedurePrice","Contract"};
    /**
     * 没有 业务类型的 动态单据设计
     */
    private final String[] noBusinessVoucherArr = {"ProductionWorkReport","NSSaleDispatchVoucher","AreaCalculation","InventoryAllocation","ReceiveVoucher", "PaymentVoucher", "ReceivableVoucher", "PayableVoucher", "OtherReceivableVoucher", "OtherPayableVoucher","ProcedurePrice","Contract"};
    /**
     * 没有 计量单位的 动态单据设计
     */
    private final String[] noUnitVoucherArr = {"ProductionWorkReport","NSSaleDispatchVoucher","AreaCalculation"};

    /**
     * 需要质检的单据
     */
    private final String[] needQualityCheckVoucherArr = {"PuArrival","ProductionFinish"};
    /**
     * 有DataSource的单据
     */
    private final String[] dataSourceVoucherArr = {"NSSaleDispatchVoucher","PurchaseInvoice","DelegateInvoice","PuArrival","DelegateArrival","SaleOrder","SampleOrder","RRS","DelegateRRS","ProductionWork","DelegateProductionWork","InventoryAllocation"};
    /**
     * 动态分页查询
     * @param dynamicVoucherDto
     * @return
     */
    public String voucherDynamicSql(DynamicVoucherDto dynamicVoucherDto, ErpVoucherTable erpVoucherTable) {
        SQL sql = new SQL();
        selectPage(sql,dynamicVoucherDto,erpVoucherTable);
        return  sql.toString();
    }


    private void selectPage(SQL sql, DynamicVoucherDto dynamicVoucherDto, ErpVoucherTable erpVoucherTable){
        UserInfo userInfo = UserThreadLocal.get();
        List<ErpVoucherDesignSearchDto> designSearchDtos = dynamicVoucherDto.getDesignSearchDtos();
        Set<String> selectSet =  new HashSet<>();
        Set<String> joinSet = new HashSet<>();
        Set<String> whereSet = new HashSet<>();
        Set<String> orderFieldSet = new HashSet<>();
        //1.获取 单据主表表名 + 明细表名6
        String tableName = erpVoucherTable.getTableName();
        String tableAlias = erpVoucherTable.getTableAlias();
        String voucherTypeCode = erpVoucherTable.getVoucherTypeCode();
        String fromTable = tableName + " " + tableAlias;

        //2.筛选 引用属性字段
        List<ErpVoucherDesignSearchDto> mianTableDesigns = designSearchDtos.stream().filter(item -> ("表头".equals(item.getDataTab()) || "表尾".equals(item.getDataTab())) && item.getOnOff()).collect(Collectors.toList());
        List<ErpVoucherDesignSearchDto> referencePropertieDesigns = mianTableDesigns.stream().filter(item-> 3 == item.getDataType()  || 4 == item.getDataType()).collect(Collectors.toList());
        referencePropertieDesigns.forEach(item->{
            String code = item.getCode();
            String[] split = code.split("\\.");
            if(split.length != 2 )throw new NuoNiuException("引用属性参数错误");
            String referenceTableName = split[0];
            String relationField = item.getRelationField();
            relationField = StringUtils.uncamelCase(relationField);
            if(StringUtils.isBlank(relationField) )throw new NuoNiuException("引用属性参数错误");
            String joinSql = referenceTableName + " ON " + tableAlias + "." + relationField + "=" + referenceTableName + ".id";
            joinSet.add(joinSql);
        });

        //3.组装 查询字段 和 查询条件
        mianTableDesigns.forEach(item->{
            Integer dataType = item.getDataType();
            Object[] rangDateVal = item.getRangDateVal();
            String type = item.getColumnType();
            String value = item.getValue();
            value = StringUtils.trim(value);
            boolean valueNotBlank = StringUtils.isNotBlank(value);
            if(dataType == 1){
                String code = item.getCode();
                String uncamelCase = StringUtils.uncamelCase(code);
                selectSet.add(tableAlias + ".id" );

                //替换操作
                if("搜索选择".equals(type) || "查询下拉".equals(type)){
                    String nameField = idFieldToNameField(code);
                    if(StringUtils.isBlank(nameField)) {
                        nameField = code + "Id";
                    }
                    String uncamelCaseName = StringUtils.uncamelCase(nameField);
                    if(StringUtils.isBlank(uncamelCaseName)){
                        uncamelCaseName = uncamelCase;
                    }
                    selectSet.add(tableAlias +"."+ uncamelCaseName +" as "+ nameField);
                    selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    orderFieldSet.add(nameField);
                    orderFieldSet.add(code);
                }else if("时间范围".equals(type)){
                    selectSet.add(tableAlias +"."+ uncamelCase +"_start as "+ code + "Start");
                    selectSet.add(tableAlias +"."+ uncamelCase +"_end as "+ code + "End");
                    orderFieldSet.add(code + "Start");
                    orderFieldSet.add(code + "End");
                }else{
                    selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    orderFieldSet.add(code);
                }

                if(valueNotBlank){
                    if("文本".equals(type) || "区域".equals(type) || "快速文本".equals(type) ){
                        //客户名称 特殊处理
                        if(code.equals("crmName")){
                            whereSet.add("(crm.crm_name LIKE CONCAT('%',#{paramMap.crmName},'%') OR crm.phonetic LIKE CONCAT('%',#{paramMap.crmName},'%'))" );
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" LIKE CONCAT('%',#{paramMap."+code+"},'%')");
                        }
                    }else if("是否".equals(type)) {
                        if("false".equals(value)){
                            whereSet.add("("+tableAlias +"."+ uncamelCase +" = '0' OR " +tableAlias +"."+ uncamelCase +" IS NULL)");
                        }else{
                            whereSet.add(tableAlias +"."+ uncamelCase +" = '1'");
                        }
                    }else {
                        whereSet.add(tableAlias +"."+ uncamelCase +" =  #{paramMap."+code+"}");
                    }
                }else if( null != rangDateVal && rangDateVal.length == 2 ){
                    if(("时间".equals(type) || "日期".equals(type))){
                        ArrayList rangDate = (ArrayList)rangDateVal[1];
                        String startDate = String.valueOf(rangDate.get(0));
                        String endDate =String.valueOf(rangDate.get(1));
                        if(StringUtils.isNoneBlank(startDate)){
                            whereSet.add(tableAlias +"."+ uncamelCase +" >= #{paramMap."+code+"_startDate}");
                        }
                        if(StringUtils.isNoneBlank(endDate)){
                            whereSet.add(tableAlias +"."+ uncamelCase +" <  #{paramMap."+code+"_endDate}");
                        }
                    }else if("时间范围".equals(type)){
                        ArrayList rangDate = (ArrayList)rangDateVal[1];
                        String startDate = String.valueOf(rangDate.get(0));
                        String endDate =String.valueOf(rangDate.get(1));
                        if(StringUtils.isNoneBlank(startDate)){
                            whereSet.add(tableAlias +"."+ uncamelCase +"_start >= #{paramMap."+code+"_startDate}");
                        }
                        if(StringUtils.isNoneBlank(endDate)){
                            whereSet.add(tableAlias +"."+ uncamelCase +"_end <  #{paramMap."+code+"_endDate}");
                        }
                    }
                }
            }else if(dataType == 2){
                String code = item.getCode();
                Long id = item.getId();
                selectSet.add(tableAlias + ".define_fields AS defineFields");
                if(valueNotBlank){
                    if("文本".equals(type) || "区域".equals(type) || "快速文本".equals(type)){
                        whereSet.add(tableAlias +".define_fields-> '$.\""+ id +"\"' LIKE CONCAT('%',#{paramMap."+code+"},'%')");
                    }else{
                        whereSet.add(tableAlias +".define_fields-> '$.\""+ id +"\"' = #{paramMap."+code+"}");
                    }
                }else if(("时间".equals(type) || "日期".equals(type)) &&  null != rangDateVal && rangDateVal.length == 2 ){
                    ArrayList rangDate = (ArrayList)rangDateVal[1];
                    String startDate = String.valueOf(rangDate.get(0));
                    String endDate =String.valueOf(rangDate.get(1));
                    if(StringUtils.isNoneBlank(startDate)){
                        whereSet.add(tableAlias +".define_fields-> '$.\""+ id +"\"' >= #{paramMap."+code+"_startDate}");
                    }
                    if(StringUtils.isNoneBlank(endDate)){
                        whereSet.add(tableAlias +".define_fields-> '$.\""+ id +"\"' < #{paramMap."+code+"_endDate}");
                    }


                }
            }else if(dataType == 3){
                String code = item.getCode();
                String uncamelCase = code + " AS '" + code + "'";
                selectSet.add(uncamelCase);
            }
        });


        //4.组装 SQL
        //4.1 主表
        sql.FROM(fromTable);

        //4.2 关联表
        joinSet.forEach(join-> sql.INNER_JOIN(join));
        //4.3 查询字段
        sql.SELECT(tableAlias + ".voucher_state AS voucherState");

        List<String> noSourceVoucherList = Arrays.asList(noSourceVoucherArr);
        if(!noSourceVoucherList.contains(voucherTypeCode)){
            sql.SELECT(tableAlias + ".source_voucher_type AS sourceVoucherType");
            sql.SELECT(tableAlias + ".source_voucher_id AS sourceVoucherId");
            sql.SELECT(tableAlias + ".source_voucher_code AS sourceVoucherCode");
            sql.SELECT(tableAlias + ".source_voucher_date AS sourceVoucherDate");
        }
        // 有业务类型 的单据
        List<String> noBusinessVoucherList = Arrays.asList(noBusinessVoucherArr);
        if( !noBusinessVoucherList.contains(voucherTypeCode) ){
            sql.SELECT(tableAlias + ".business_type AS businessType");
        }
        sql.SELECT(StringUtils.join(selectSet,","));
        //4.4 查询条件
        /*********  获取 单据状态 查询SQL  开始 *******/
        String voucherStateSql = getLastSqlFromVoucherState(tableAlias,dynamicVoucherDto);
        if(StringUtils.isNotBlank(voucherStateSql)){
            sql.WHERE("("+voucherStateSql+")");
        }
        /*********  获取 单据状态 查询SQL  结束 *******/
        Integer businessType = dynamicVoucherDto.getBusinessType();
        String someBusinessType = dynamicVoucherDto.getSomeBusinessType();

        /********************  退货业务 --START-- ***********************/
        // 1: 退货  2.非退货 3.退货 + 非退货  4:开票 5.销售订单 是否生成主生产计划 6.质检数量
        if (null != businessType){
            switch (businessType){
                case 1: sql.WHERE( "(" + tableAlias + ".total_quantity > " + tableAlias + ".cum_ret_quantity  )"); break;
                case 2: sql.WHERE( "(" + tableAlias + ".total_quantity > " + tableAlias + ".cum_execute_quantity )"); break;
                case 3: sql.WHERE( "((" + tableAlias + ".total_quantity > " + tableAlias + ".cum_ret_quantity  ) OR (" + tableAlias + ".total_quantity > " + tableAlias + ".cum_execute_quantity ))");
                case 4: sql.WHERE( "(" + tableAlias + ".total_quantity > " + tableAlias + ".cum_invoice_quantity  )"); break;
                // 是否生成 主生产计划
                case 5: sql.WHERE(tableAlias + ".is_generate_work_plan = 0");  break;
                case 6: sql.WHERE( "(" + tableAlias + ".total_quantity > " + tableAlias + ".cum_quality_check_quantity )"); break;
            }
        }

        /******************** 退货业务 --END-- ***********************/

        /******************** 处理 指定 业务类型 开始***********************/
        if (StringUtils.isNotBlank(someBusinessType)){
            boolean matches = someBusinessType.matches(STR_ENG_PATTERN);
            if (!matches)throw new NuoNiuException("指定业务类型 参数错误");
            sql.WHERE(tableAlias + ".business_type  in ("+someBusinessType+")");

        }
        /******************** 处理 指定 业务类型 结束***********************/

        /******************** 处理仓库数据权限 -- 开始 ***********************/
        List<Long> inWarehouseIds = dynamicVoucherDto.getInWarehouseIds();
        Boolean isSelectCreateData = dynamicVoucherDto.getIsSelectCreateData();
        if(null != inWarehouseIds && inWarehouseIds.size() > 0){
            String inWarehouseIdsStr = StringUtils.join(inWarehouseIds, ",");
            if (null != isSelectCreateData && isSelectCreateData){
                sql.WHERE( " ("+ tableAlias + ".warehouse_id in (" + inWarehouseIdsStr + ")  OR  "+ tableAlias +".create_user_id = " + userInfo.getUserId() + ")");
            }else{
                sql.WHERE(tableAlias + ".warehouse_id in (" + inWarehouseIdsStr + ")");
            }
        }
        if (null != isSelectCreateData && isSelectCreateData){
            sql.WHERE( tableAlias +".create_user_id = " + userInfo.getUserId());
        }

        /******************** 处理仓库数据权限 -- 结束 ***********************/

        /******************** 处理  单据数据来源 -- 开始 ***********************/
        if(tableName.equals("scw_delivery_entry")){
            sql.WHERE(tableAlias + ".data_source = '" + voucherTypeCode + "'");
            sql.SELECT(tableAlias + ".receive_deliver_type AS receiveDeliverType");
        }
        String dataSource = dynamicVoucherDto.getDataSource();
        List<String> dataSourceVoucherList = Arrays.asList(dataSourceVoucherArr);
        if(StringUtils.isNotBlank(dataSource) && dataSourceVoucherList.contains(voucherTypeCode)){
            switch (dataSource){
                default: sql.WHERE(tableAlias + ".data_source = '" + dataSource +"'");break;
            }
        }

        /******************** 处理  单据数据来源 -- 结束 ***********************/

        sql.WHERE(tableAlias + ".company_id =" + userInfo.getCompanyId());
        sql.WHERE(tableAlias + ".account_id = #{currentAccountId}");


        whereSet.forEach(whereSql->sql.WHERE(whereSql));

        //排序
        String sortField = dynamicVoucherDto.getSortField();
        String sortOrder = dynamicVoucherDto.getSortOrder();
        if(StringUtils.isNoneBlank(sortField) && StringUtils.isNoneBlank(sortOrder) &&  orderFieldSet.contains(sortField)){
            String orderType = " DESC";
            if(StringUtils.isNoneBlank(sortOrder) && "ascend".equals(sortOrder)){
                orderType = " ASC";
            }
            sql.ORDER_BY( sortField + orderType);
        }else{
            sql.ORDER_BY(tableAlias + ".id desc");
        }



    }

    private String idFieldToNameField(String idFieldTo){
        String result = "";
        if(StringUtils.isNoneBlank(idFieldTo) &&  idFieldTo.endsWith(ID_FIELD)){
            String s = idFieldTo.substring(0, idFieldTo.lastIndexOf(ID_FIELD));
            result = s + NAME_FIELD;
        }
        return result;
    }


    /**
     * 获取 单据状态 查询SQL
     * @param dynamicVoucherDto
     * @return
     */
    private String getLastSqlFromVoucherState(String tableAlias, DynamicVoucherDto dynamicVoucherDto){
        String voucherStateSql = "";
        List<Integer> voucherStateList = dynamicVoucherDto.getVoucherStateList();
        Integer startVoucherState = dynamicVoucherDto.getStartVoucherState();
        Integer endVoucherState = dynamicVoucherDto.getEndVoucherState();
        if (null != startVoucherState || null != endVoucherState || (null != voucherStateList && voucherStateList.size() > 0)){

            if (null != voucherStateList && voucherStateList.size() > 0){
                String voucherStateListJoinStr = StringUtils.join(voucherStateList, ",");
                voucherStateSql += " OR "+tableAlias+".voucher_state in (" +voucherStateListJoinStr+ ")";
            }
            if(null != startVoucherState && null == endVoucherState ){
                voucherStateSql += " OR "+tableAlias+".voucher_state > " + startVoucherState;
            }
            if(null != endVoucherState && null == startVoucherState ){
                voucherStateSql += " OR "+tableAlias+".voucher_state < " + endVoucherState;
            }
            if(null != startVoucherState && null != endVoucherState){
                voucherStateSql += " OR (" + tableAlias+".voucher_state > " + startVoucherState +" AND "+ tableAlias+".voucher_state < " + endVoucherState +")";
            }
            if (StringUtils.startsWith(voucherStateSql," OR")){
                voucherStateSql = StringUtils.replaceOnce(voucherStateSql," OR","");
            }
        }
        return voucherStateSql;
    }

    /******************************** 查询详情 **********************************/
    /**
     * 单据主表 查询
     * @param designSearchDtos
     * @param erpVoucherTable
     * @return
     */
    public String voucherDynamicByIdSql(List<ErpVoucherDesign> designSearchDtos, ErpVoucherTable erpVoucherTable) {
        SQL sql = new SQL();
        selectMain(sql,designSearchDtos,erpVoucherTable);
        return  sql.toString();
    }

    /**
     * 主表查询
     * @param sql
     * @param designSearchDtos
     * @param erpVoucherTable
     */
    private void selectMain(SQL sql , List<ErpVoucherDesign> designSearchDtos , ErpVoucherTable erpVoucherTable){
        UserInfo userInfo = UserThreadLocal.get();
        Set<String> selectSet =  new HashSet<>();
        Set<String> joinSet = new HashSet<>();
        Set<String> whereSet = new HashSet<>();
        //1.获取 单据主表表名 + 明细表名
        String tableName = erpVoucherTable.getTableName();
        String tableAlias = erpVoucherTable.getTableAlias();
        String voucherTypeCode = erpVoucherTable.getVoucherTypeCode();
        String fromTable = tableName + " " + tableAlias;

        //2.筛选 引用属性字段
        List<ErpVoucherDesign> mianTableDesigns = designSearchDtos.stream().filter(item -> ("表头".equals(item.getDataTab()) || "表尾".equals(item.getDataTab())) && item.getOnOff()).collect(Collectors.toList());
        mianTableDesigns.forEach(item->{
            String columnType = item.getColumnType();
            Integer dataType = item.getDataType();
            String code = item.getCode();
            String uncamelCase;
            switch (dataType){
                case 1:
                    uncamelCase = StringUtils.uncamelCase(code);
                    if("搜索选择".equals(columnType) || "查询下拉".equals(columnType)){
                        String nameField = idFieldToNameField(code);
                        String uncamelCaseName = StringUtils.uncamelCase(nameField);
                        if(StringUtils.isBlank(uncamelCaseName)){
                            uncamelCaseName = uncamelCase;
                        }
                        selectSet.add(tableAlias +"."+ uncamelCaseName +" as "+ nameField);
                        selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    }else{
                        selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    }
                    break;
                case 2: selectSet.add(tableAlias + ".define_fields AS defineFields");break;
                case 3:
                    String[] codeArr = code.split("\\.");
                    String leftJoinTable = codeArr[0];
                    String leftJoinTableField = codeArr[1];
                    uncamelCase = StringUtils.uncamelCase(leftJoinTableField);
                    String nameField = idFieldToNameField(leftJoinTableField);
                    if(StringUtils.isNotBlank(nameField)){
                        String uncamelCaseName = StringUtils.uncamelCase(nameField);
                        if(StringUtils.isBlank(uncamelCaseName)){
                            uncamelCaseName = uncamelCase;
                        }
                        selectSet.add(leftJoinTable +"."+ uncamelCaseName +" as "+ code);
                    }
                    break;
                case 4:
                    String[] codeArr2 = code.split("\\.");
                    String leftJoinTable2 = codeArr2[0];
                    String leftJoinTableField2 = codeArr2[1];
                    uncamelCase = leftJoinTable2 + ".define_fields->>" + "'$." +leftJoinTableField2 + "' AS " + code;
                    selectSet.add(uncamelCase);
                    break;
            }


        });
        List<ErpVoucherDesign> referencePropertieDesigns = mianTableDesigns.stream().filter(item-> 3 == item.getDataType() || 4 == item.getDataType()).collect(Collectors.toList());
        referencePropertieDesigns.forEach(item->{
            String code = item.getCode();
            String[] split = code.split("\\.");
            if(split.length != 2 )throw new NuoNiuException("引用属性参数错误");
            String referenceTableName = split[0];
            String relationField = item.getRelationField();
            relationField = StringUtils.uncamelCase(relationField);
            if(StringUtils.isBlank(relationField) )throw new NuoNiuException("引用属性参数错误");
            String joinSql = referenceTableName + " ON " + tableAlias + "." + relationField + "=" + referenceTableName + ".id";
            joinSet.add(joinSql);
        });
        //

        //3.组装 SQL
        //3.1 主表
        sql.FROM(fromTable);
        //3.2 关联表
        joinSet.forEach(join-> sql.LEFT_OUTER_JOIN(join));
        //3.3 查询字段
        sql.SELECT(tableAlias + ".id");
        List<String> noSourceVoucherList = Arrays.asList(noSourceVoucherArr);
        if(!noSourceVoucherList.contains(voucherTypeCode)){
            sql.SELECT(tableAlias + ".source_voucher_date AS sourceVoucherDate");
            sql.SELECT(tableAlias + ".source_voucher_type AS sourceVoucherType");
            sql.SELECT(tableAlias + ".source_voucher_id AS sourceVoucherId");
            sql.SELECT(tableAlias + ".source_voucher_code AS sourceVoucherCode");
        }
        sql.SELECT(tableAlias + ".voucher_state AS voucherState");
        sql.SELECT(StringUtils.join(selectSet,","));
        //3.4 查询条件
        //如果是 出入库单据，则添加 ‘数据来源’条件进行筛选
        if(tableName.equals("scw_delivery_entry")){
            sql.WHERE(tableAlias + ".data_source = '" + voucherTypeCode + "'");
            sql.SELECT(tableAlias + ".receive_deliver_type AS receiveDeliverType");
        }
        List<String> noBusinessVoucherList = Arrays.asList(noBusinessVoucherArr);
        if( !noBusinessVoucherList.contains(voucherTypeCode) ){
            sql.SELECT(tableAlias + ".business_type AS businessType");
        }

        sql.WHERE(tableAlias + ".company_id =" + userInfo.getCompanyId());
        sql.WHERE(tableAlias + ".account_id = #{currentAccountId}");
        sql.WHERE(tableAlias + ".id = #{id}");

    }


    /**
     * 单据 明细查询
     * @param designSearchDtos
     * @param erpVoucherTable
     * @return
     */
    public String voucherDynamicDetailListByIdSql(List<ErpVoucherDesign> designSearchDtos, DynamicVoucherDto dynamicVoucherDto, ErpVoucherTable erpVoucherTable) {
        SQL sql = new SQL();
        selectDetail(sql,designSearchDtos,dynamicVoucherDto,erpVoucherTable);
        return  sql.toString();
    }

    /**
     * 明细查询
     * @param sql
     * @param designSearchDtos
     * @param erpVoucherTable
     */
    private void selectDetail(SQL sql , List<ErpVoucherDesign> designSearchDtos , DynamicVoucherDto dynamicVoucherDto, ErpVoucherTable erpVoucherTable){
        UserInfo userInfo = UserThreadLocal.get();
        Set<String> selectSet =  new HashSet<>();
        Set<String> joinSet = new HashSet<>();
        //1.获取 单据主表表名 + 明细表名
        String voucherTypeCode = erpVoucherTable.getVoucherTypeCode();
        String tableName = erpVoucherTable.getDetailTableName();
        String tableAlias = erpVoucherTable.getDetailTableAlias();
        String fromTable = tableName + " " + tableAlias;

        //2.筛选 引用属性字段
        List<ErpVoucherDesign> mianTableDesigns = designSearchDtos.stream().filter(item -> ("明细".equals(item.getDataTab())) && item.getOnOff()).collect(Collectors.toList());
        mianTableDesigns.forEach(item->{
            String columnType = item.getColumnType();
            Integer dataType = item.getDataType();
            String code = item.getCode();
            String uncamelCase;
            switch (dataType){
                case 1:
                    uncamelCase = StringUtils.uncamelCase(code);
                    if("搜索选择".equals(columnType) || "查询下拉".equals(columnType)){
                        String nameField = idFieldToNameField(code);
                        String uncamelCaseName = StringUtils.uncamelCase(nameField);
                        if(StringUtils.isBlank(uncamelCaseName)){
                            uncamelCaseName = uncamelCase;
                        }
                        selectSet.add(tableAlias +"."+ uncamelCaseName +" as "+ nameField);
                        selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    }else{
                        selectSet.add(tableAlias +"."+ uncamelCase +" as "+ code);
                    }
                    if("baseUnit".equals(code) || "subUnit".equals(code)){
                        selectSet.add(tableAlias +"."+ uncamelCase +"_name as "+ code +"Name");
                    }
                    break;
                case 2: selectSet.add(tableAlias + ".define_fields AS defineFields");break;
                case 3:
                    String[] codeArr = code.split("\\.");
                    String leftJoinTable = codeArr[0];
                    String leftJoinTableField = codeArr[1];
                    String nameField = idFieldToNameField(leftJoinTableField);
                    if(StringUtils.isBlank(nameField)){
                        nameField = leftJoinTableField;
                    }
                    uncamelCase = StringUtils.uncamelCase(nameField);

                    selectSet.add(leftJoinTable +"."+ uncamelCase +" as '"+ code +"'");
                    break;
                case 4:
                    String[] codeArr2 = code.split("\\.");
                    String leftJoinTable2 = codeArr2[0];
                    String leftJoinTableField2 = codeArr2[1];
                    uncamelCase = leftJoinTable2 + ".define_fields->>" + "'$." +leftJoinTableField2 + "' AS '" + code + "'";
                    selectSet.add(uncamelCase);
                    break;
            }

        });
        List<ErpVoucherDesign> referencePropertieDesigns = mianTableDesigns.stream().filter(item-> 3 == item.getDataType()  || 4 == item.getDataType()).collect(Collectors.toList());
        referencePropertieDesigns.forEach(item->{
            String code = item.getCode();
            String[] split = code.split("\\.");
            if(split.length != 2 )throw new NuoNiuException("引用属性参数错误");
            String referenceTableName = split[0];
            String relationField = item.getRelationField();
            relationField = StringUtils.uncamelCase(relationField);
            if(StringUtils.isBlank(relationField) )throw new NuoNiuException("引用属性参数错误");
            String joinSql = referenceTableName + " ON " + tableAlias + "." + relationField + "=" + referenceTableName + ".id";
            joinSet.add(joinSql);
        });

        List<String> puVoucherCodes = Arrays.asList(PuVoucherCodesArr);
        List<String> saVoucherCodes = Arrays.asList(SaVoucherCodesArr);
        List<String> noSourceVoucherList = Arrays.asList(noSourceVoucherArr);
        List<String> noUnitVoucherList = Arrays.asList(noUnitVoucherArr);
        //3.组装 SQL
        //3.1 主表
        sql.FROM(fromTable);
        //3.2 关联表
        joinSet.forEach(join-> sql.LEFT_OUTER_JOIN(join));
        //3.3 查询字段
        if(!Arrays.asList(FVoucherCodeArr).contains(erpVoucherTable.getVoucherTypeCode())) { //判断是否财务单据
            if(puVoucherCodes.contains(voucherTypeCode) || saVoucherCodes.contains(voucherTypeCode)){
                sql.SELECT(tableAlias + ".cum_ret_quantity AS cumRetQuantity");    //累计退货数量
            }
            if (!noSourceVoucherList.contains(voucherTypeCode)){
                sql.SELECT(tableAlias + ".source_detail_id AS sourceDetailId");
                sql.SELECT(tableAlias + ".cum_execute_quantity AS cumExecuteQuantity");    //累计执行数量
            }
            if(!noUnitVoucherList.contains(voucherTypeCode)){
                sql.SELECT(tableAlias + ".unit_id AS unitId");
                sql.SELECT(tableAlias + ".unit_group_id AS unitGroupId");
            }
        }
        // 工序汇报 比传回 参数
        if("ProductionWorkReport".equals(voucherTypeCode)){

            sql.SELECT(tableAlias + ".wages_type AS wagesType");
            sql.SELECT(tableAlias + ".process_route_detail_id AS processRouteDetailId");
            sql.SELECT(tableAlias + ".production_work_procedure_detail_Id AS productionWorkProcedureDetailId");


            sql.SELECT(tableAlias + ".qualified_price AS qualifiedPrice");
            sql.SELECT(tableAlias + ".rework_price AS reworkPrice");
            sql.SELECT(tableAlias + ".unqualified_price AS unqualifiedPrice");
            sql.SELECT(tableAlias + ".scrap_price AS scrapPrice");
            sql.SELECT(tableAlias + ".work_hour_price AS workHourPrice");
        }

        sql.SELECT(tableAlias + ".id");

        if(!noSourceVoucherList.contains(voucherTypeCode)){
            sql.SELECT(tableAlias + ".source_voucher_type AS sourceVoucherType");
            sql.SELECT(tableAlias + ".source_voucher_date AS sourceVoucherDate");
            sql.SELECT(tableAlias + ".source_voucher_id AS sourceVoucherId");
            sql.SELECT(tableAlias + ".source_voucher_code AS sourceVoucherCode");
        }

        if("RRS".equals(voucherTypeCode) || "DelegateRRS".equals(voucherTypeCode)){
            sql.SELECT(tableAlias + ".source_production_work_id AS sourceProductionWorkId"); //来源加工单ID
            sql.SELECT(tableAlias + ".source_work_inventory_detail_id AS sourceWorkInventoryDetailId"); //来源派工单材料明细ID
        }
        /**  需要质检的单据 **/
        List<String> needQualityCheckVoucherList = Arrays.asList(needQualityCheckVoucherArr);
        if (needQualityCheckVoucherList.contains(voucherTypeCode)){
            sql.SELECT(tableAlias + ".cum_quality_check_quantity AS cumQualityCheckQuantity");    //累计报检数量
        }

        sql.SELECT(tableAlias + ".order_id AS orderId");
        sql.SELECT(StringUtils.join(selectSet,","));
        //3.4 查询条件
        sql.WHERE(tableAlias + ".company_id =" + userInfo.getCompanyId());
        sql.WHERE(tableAlias + ".order_id in (${ids})");

        Integer businessType = dynamicVoucherDto.getBusinessType();
        if (null != businessType){
            switch (businessType){
                //生产计划选单
                case 5:
                    // 如果是销售订单，才可以 添加以下SQL
                    if ("SaleOrder".equals(voucherTypeCode)){
                        sql.WHERE(tableAlias + ".is_generate_work_plan = 0");  // 是否生成 主生产计划
                    }
                    break;

            }
        }

    }

    /**
     *
     * @param erpVoucherDesignList
     * @param erpVoucherTable
     * @param detailIds
     * @return
     */
   public String voucherDynamicSpecifyFieldRecordSql(List<ErpVoucherDesign> erpVoucherDesignList, ErpVoucherTable erpVoucherTable, List<Integer> detailIds){
       SQL sql = new SQL();
       getSpecifyFieldRecord(sql,erpVoucherDesignList,erpVoucherTable,detailIds);
       return  sql.toString();
   }


   private void getSpecifyFieldRecord(SQL sql, List<ErpVoucherDesign> erpVoucherDesignList, ErpVoucherTable erpVoucherTable, List<Integer> detailIds){
       UserInfo userInfo = UserThreadLocal.get();
       Set<String> selectSet =  new HashSet<>();
       Set<String> joinSet = new HashSet<>();
       //1.获取 单据主表表名 + 明细表名
       String tableName = erpVoucherTable.getTableName();
       String tableAlias = erpVoucherTable.getTableAlias();
       String fromTable = tableName + " " + tableAlias;

       String detailTableName = erpVoucherTable.getDetailTableName();
       String detailTableAlias = erpVoucherTable.getDetailTableAlias();
       String detailTable = detailTableName + " " + detailTableAlias;

       erpVoucherDesignList.forEach(item->{
           String dataTab = item.getDataTab();
           String columnType = item.getColumnType();
           Integer dataType = item.getDataType();
           String code = item.getCode();
           String uncamelCase;
           String alias = tableAlias;
           if(dataTab.equals("明细")){
               alias = detailTableAlias;
           }
           switch (dataType){
               case 1:
                   uncamelCase = StringUtils.uncamelCase(code);
                   if("搜索选择".equals(columnType) || "查询下拉".equals(columnType)){
                       String nameField = idFieldToNameField(code);
                       String uncamelCaseName = StringUtils.uncamelCase(nameField);
                       if(StringUtils.isBlank(uncamelCaseName)){
                           uncamelCaseName = uncamelCase;
                       }
                       selectSet.add(alias +"."+ uncamelCaseName +" as "+ nameField);
                       selectSet.add(alias +"."+ uncamelCase +" as "+ code);
                   }else{
                       selectSet.add(alias +"."+ uncamelCase +" as "+ code);
                   }
                   break;
               case 2: selectSet.add(alias + ".define_fields AS defineFields");break;
               case 3: uncamelCase = code + " AS '" + code + "'";selectSet.add(uncamelCase);break;
           }
       });
       //3.组装 SQL
       //3.1 主表
       sql.FROM(fromTable);
       //3.2 关联明细表
       String joinSql = detailTable + " ON " + detailTableAlias + ".order_id =" + tableAlias + ".id";
       sql.INNER_JOIN(joinSql);
       sql.SELECT(StringUtils.join(selectSet,","));
       sql.SELECT(detailTableAlias +".id AS sourceDetailId");
       sql.WHERE(tableAlias +".company_id = " + userInfo.getCompanyId());
       String detailIdsStr = StringUtils.join(detailIds,",");
       sql.WHERE(detailTableAlias +".id in ("+ detailIdsStr +")");
   }



    public String listNyVoucherTableSql(ErpVoucherTable erpVoucherTable, List<Integer> detailIds){
        SQL sql = new SQL();
        listNyVoucherTable(sql,erpVoucherTable,detailIds);
        return  sql.toString();
    }

    private void listNyVoucherTable(SQL sql, ErpVoucherTable erpVoucherTable, List<Integer> detailIds){
        UserInfo userInfo = UserThreadLocal.get();
        Set<String> selectSet =  new HashSet<>();
        //1.获取 单据主表表名 + 明细表名
        String tableName = erpVoucherTable.getTableName();
        String tableAlias = erpVoucherTable.getTableAlias();
        String fromTable = tableName + " " + tableAlias;

        String detailTableName = erpVoucherTable.getDetailTableName();
        String detailTableAlias = erpVoucherTable.getDetailTableAlias();
        String detailTable = detailTableName + " " + detailTableAlias;


        //3.组装 SQL
        //3.1 主表
        sql.FROM(fromTable);
        selectSet.add(tableAlias +".* " );
        //3.2 关联明细表
        String joinSql = detailTable + " ON " + detailTableAlias + ".order_id =" + tableAlias + ".id";
        sql.INNER_JOIN(joinSql);
        selectSet.add(detailTableAlias +".* " );
        selectSet.add(tableAlias +".id AS sourceVoucherId " );
        selectSet.add(tableAlias +".voucher_code AS sourceVoucherCode" );
        selectSet.add(tableAlias +".voucher_date AS sourceVoucherDate " );
        selectSet.add(detailTableAlias +".id AS sourceDetailId " );
        sql.SELECT(StringUtils.join(selectSet,","));
        sql.WHERE(tableAlias +".company_id = " + userInfo.getCompanyId());
        String detailIdsStr = StringUtils.join(detailIds,",");
        sql.WHERE(detailTableAlias +".id in ("+ detailIdsStr +")");
    }

}
